﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using IFRS.DataAccess.DAL;
using IFRS.Models;
namespace IFRS.DataAccess.DAL
{
    public class SPR_MANUAL_LOAN
    {
        public DataTable SEARCH_ALL(ManualLoan manualLoan, Paging paging, bool IFRS_ALLBRANCH, string sIFRS_COST_CENTER, ref int iRecordCount)
        {
            DataTable dt = new DataTable();
            IFRS_Utility Utility = new IFRS_Utility();
            List<SqlParameter> lstParams = new List<SqlParameter>();
            try
            {
                lstParams.Add(new SqlParameter("@RecordCount", SqlDbType.Int));
                lstParams[0].Direction = ParameterDirection.Output;
                lstParams.Add(new SqlParameter("@PageIndex", paging.PageIndex));
                lstParams.Add(new SqlParameter("@PageSize", paging.PageSize));
                lstParams.Add(new SqlParameter("@Sorting", paging.Sorting));
                lstParams.Add(new SqlParameter("@isAllBranch", IFRS_ALLBRANCH));
                lstParams.Add(new SqlParameter("@sCostCenter", sIFRS_COST_CENTER));
                lstParams.Add(new SqlParameter("@TransactionDate", manualLoan.TransactionDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@AccountNo", manualLoan.AccountNo.AsDBValue()));
                lstParams.Add(new SqlParameter("@CostCenter", manualLoan.CostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@IsConfirmed", manualLoan.IsConfirmed.AsDBValue()));
                dt = Utility.StoredProcDataTable("SPR_MANUALLOAN_SEARCHALL", lstParams.ToArray());

                iRecordCount = (int)lstParams[0].Value;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public DataTable SEARCH_BYID(int ManualLoanID)
        {
            DataTable dt = new DataTable();
            IFRS_Utility Utility = new IFRS_Utility();
            List<SqlParameter> lstParams = new List<SqlParameter>();
            try
            {
                lstParams.Add(new SqlParameter("@ManualLoanID", ManualLoanID.AsDBValue()));
                dt = Utility.StoredProcDataTable("SPR_MANUALLOAN_SEARCHBYID", lstParams.ToArray());

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt;
        }

        public int INSERT(ManualLoan manualLoan)
        {

            int iNum = 0;
            IFRS_Utility Utility = new IFRS_Utility();
            List<SqlParameter> lstParams = new List<SqlParameter>();

            try
            {
                lstParams.Add(new SqlParameter("@ManualLoanID", SqlDbType.Int));
                lstParams[0].Direction = ParameterDirection.Output;
                lstParams.Add(new SqlParameter("@TransactionDate", manualLoan.TransactionDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@AccountNo", manualLoan.AccountNo.AsDBValue()));
                lstParams.Add(new SqlParameter("@GLAccount", manualLoan.GLAccount.AsDBValue()));
                lstParams.Add(new SqlParameter("@CostCenter", manualLoan.CostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@StartedDate", manualLoan.StartedDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@EndDate", manualLoan.EndDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@LastPayDate", manualLoan.LastPayDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@NextPayDate", manualLoan.NextPayDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@PaymentFreq", manualLoan.PaymentFreq.AsDBValue()));
                lstParams.Add(new SqlParameter("@PaymentFreqTimeBase", manualLoan.PaymentFreqTimeBase.AsDBValue()));
                lstParams.Add(new SqlParameter("@PaymentConditionID", manualLoan.PaymentConditionID.AsDBValue()));
                lstParams.Add(new SqlParameter("@CalInterestCondID", manualLoan.CalInterestCondID.AsDBValue()));
                lstParams.Add(new SqlParameter("@InterestTypeID", manualLoan.InterestTypeID.AsDBValue()));
                lstParams.Add(new SqlParameter("@InterestRate", manualLoan.InterestRate.AsDBValue()));
                lstParams.Add(new SqlParameter("@RefInterestCode", manualLoan.RefInterestCode.AsDBValue()));
                lstParams.Add(new SqlParameter("@AdditionalInterest", manualLoan.AdditionalInterest.AsDBValue()));
                lstParams.Add(new SqlParameter("@DayCountMethodID", manualLoan.DayCountMethodID.AsDBValue()));
                lstParams.Add(new SqlParameter("@CurrencyCode", manualLoan.CurrencyCode.AsDBValue()));
                lstParams.Add(new SqlParameter("@UnpaidInterest", manualLoan.UnpaidInterest.AsDBValue()));
                lstParams.Add(new SqlParameter("@Dept", manualLoan.Dept.AsDBValue()));
                lstParams.Add(new SqlParameter("@CreditLevelID", manualLoan.CreditLevelID.AsDBValue()));
                lstParams.Add(new SqlParameter("@CustomerID", manualLoan.CustomerID.AsDBValue()));
                lstParams.Add(new SqlParameter("@ContractLoanTypeID", manualLoan.ContractLoanTypeID.AsDBValue()));
                lstParams.Add(new SqlParameter("@LoanObjectiveID", manualLoan.LoanObjectiveID.AsDBValue()));
                lstParams.Add(new SqlParameter("@FisrtMisDate", manualLoan.FisrtMisDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@AdjustFlag", manualLoan.AdjustFlag.AsDBValue()));
                lstParams.Add(new SqlParameter("@ReservedAmount", manualLoan.ReservedAmount.AsDBValue()));
                lstParams.Add(new SqlParameter("@ApprovedAmount", manualLoan.ApprovedAmount.AsDBValue()));
                lstParams.Add(new SqlParameter("@IsConfirmed", manualLoan.IsConfirmed.AsDBValue()));
                lstParams.Add(new SqlParameter("@RP_GSB_PRDCD", manualLoan.RP_GSB_PRDCD.AsDBValue()));
                lstParams.Add(new SqlParameter("@RP_SOURCE_FILE", manualLoan.RP_SOURCE_FILE.AsDBValue()));
                lstParams.Add(new SqlParameter("@RP_MAPPING_CD", manualLoan.RP_MAPPING_CD.AsDBValue()));                
                lstParams.Add(new SqlParameter("@CreatedDate", manualLoan.CreatedDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@CreatedBy", manualLoan.CreatedBy.AsDBValue()));
                lstParams.Add(new SqlParameter("@CreatedCostCenter", manualLoan.CreatedCostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@CreatedBranch", manualLoan.CreatedBranch.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedDate", manualLoan.UpdatedDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBy", manualLoan.UpdatedBy.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedCostCenter", manualLoan.UpdatedCostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBranch", manualLoan.UpdatedBranch.AsDBValue()));


                Utility.ExecuteStoredProc("SPR_MANUALLOAN_INSERT", lstParams.ToArray());

                iNum = (int)lstParams[0].Value;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return iNum;
        }

        public int UPDATE(ManualLoan manualLoan)
        {


            int iNum = 0;
            IFRS_Utility Utility = new IFRS_Utility();
            List<SqlParameter> lstParams = new List<SqlParameter>();
            try
            {
                lstParams.Add(new SqlParameter("@ManualLoanID", manualLoan.ManualLoanID.AsDBValue()));
                lstParams.Add(new SqlParameter("@TransactionDate", manualLoan.TransactionDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@AccountNo", manualLoan.AccountNo.AsDBValue()));
                lstParams.Add(new SqlParameter("@GLAccount", manualLoan.GLAccount.AsDBValue()));
                lstParams.Add(new SqlParameter("@CostCenter", manualLoan.CostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@StartedDate", manualLoan.StartedDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@EndDate", manualLoan.EndDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@LastPayDate", manualLoan.LastPayDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@NextPayDate", manualLoan.NextPayDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@PaymentFreq", manualLoan.PaymentFreq.AsDBValue()));
                lstParams.Add(new SqlParameter("@PaymentFreqTimeBase", manualLoan.PaymentFreqTimeBase.AsDBValue()));
                lstParams.Add(new SqlParameter("@PaymentConditionID", manualLoan.PaymentConditionID.AsDBValue()));
                lstParams.Add(new SqlParameter("@CalInterestCondID", manualLoan.CalInterestCondID.AsDBValue()));
                lstParams.Add(new SqlParameter("@InterestTypeID", manualLoan.InterestTypeID.AsDBValue()));
                lstParams.Add(new SqlParameter("@InterestRate", manualLoan.InterestRate.AsDBValue()));
                lstParams.Add(new SqlParameter("@RefInterestCode", manualLoan.RefInterestCode.AsDBValue()));
                lstParams.Add(new SqlParameter("@AdditionalInterest", manualLoan.AdditionalInterest.AsDBValue()));
                lstParams.Add(new SqlParameter("@DayCountMethodID", manualLoan.DayCountMethodID.AsDBValue()));
                lstParams.Add(new SqlParameter("@CurrencyCode", manualLoan.CurrencyCode.AsDBValue()));
                lstParams.Add(new SqlParameter("@UnpaidInterest", manualLoan.UnpaidInterest.AsDBValue()));
                lstParams.Add(new SqlParameter("@Dept", manualLoan.Dept.AsDBValue()));
                lstParams.Add(new SqlParameter("@CreditLevelID", manualLoan.CreditLevelID.AsDBValue()));
                lstParams.Add(new SqlParameter("@CustomerID", manualLoan.CustomerID.AsDBValue()));
                lstParams.Add(new SqlParameter("@ContractLoanTypeID", manualLoan.ContractLoanTypeID.AsDBValue()));
                lstParams.Add(new SqlParameter("@LoanObjectiveID", manualLoan.LoanObjectiveID.AsDBValue()));
                lstParams.Add(new SqlParameter("@FisrtMisDate", manualLoan.FisrtMisDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@AdjustFlag", manualLoan.AdjustFlag.AsDBValue()));
                lstParams.Add(new SqlParameter("@ReservedAmount", manualLoan.ReservedAmount.AsDBValue()));
                lstParams.Add(new SqlParameter("@ApprovedAmount", manualLoan.ApprovedAmount.AsDBValue()));
                lstParams.Add(new SqlParameter("@IsConfirmed", manualLoan.IsConfirmed.AsDBValue()));                        
                lstParams.Add(new SqlParameter("@UpdatedDate", manualLoan.UpdatedDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBy", manualLoan.UpdatedBy.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedCostCenter", manualLoan.UpdatedCostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBranch", manualLoan.UpdatedBranch.AsDBValue()));

                iNum = (int)Utility.ExecuteStoredProc("SPR_MANUALLOAN_UPDATE", lstParams.ToArray());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return iNum;
        }

        public int DELETE(ManualLoan manualLoan)
        {
            int iNum = 0;
            IFRS_Utility Utility = new IFRS_Utility();
            List<SqlParameter> lstParams = new List<SqlParameter>();
            try
            {
                lstParams.Add(new SqlParameter("@ManualLoanID", manualLoan.ManualLoanID.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedDate", manualLoan.UpdatedDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBy", manualLoan.UpdatedBy.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedCostCenter", manualLoan.UpdatedCostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBranch", manualLoan.UpdatedBranch.AsDBValue()));

                iNum = (int)Utility.ExecuteStoredProc("SPR_MANUALLOAN_DELETE", lstParams.ToArray());

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return iNum;
        }
    }
}
